<?php
class QueryCompilingTest
{
    protected function getQuery () {
        return DatabaseFactory::create('MySQL')
            ->connect    ('localhost', 'Shock', '')
            ->setDbName  ('php')
            ->setCharset ('utf8')
            ->setPrefix  ('DB')
            ->query();
    }

    public function select () {
        $id = 20;
        $query = $this->getQuery()->select()
        ->fields('*', 'COUNT( `Post`.`ID` ) AS `Posts`')
        ->table("Posts", "Post")
        ->innerJoin("Authors", "Author", "`Author`.`ID` = `Post`.`AuthorID`")
        ->innerJoin("Posts", "PostCount", "`Author`.`ID` = `PostCount`.`AuthorID`")
        ->where("`Post`.`Deleted` = %d", 0)
        ->where("`PostCount`.`Deleted` = %d", 0)
        ->group("`Post`.`ID`")
        ->limit(0, 30)
        ->order('`Post`.`Date`', 1) // DESC
        ->order('`Post`.`ID`') // ASC
        ->compile();
        echo $query->getQuery();
        /*
SELECT *, COUNT( `Post`.`ID` ) AS `Posts`
FROM `DB_Posts` AS `Post`
INNER JOIN `DB_Authors` AS `Author`
  ON `Author`.`ID` = `Post`.`AuthorID`
INNER JOIN `DB_Posts` AS `PostCount`
  ON `Author`.`ID` = `PostCount`.`AuthorID`
WHERE `Post`.`Deleted` = 0
  AND `PostCount`.`Deleted` = 0
GROUP BY `Post`.`ID`
ORDER BY
  `Post`.`Date` DESC,
  `Post`.`ID` ASC
LIMIT 0, 30
         */
    }

    public function update ($value = 1) {
        $id = 5;
        $query = $this->getQuery()
            ->update ()
            ->table ('Posts')
            ->set   ("`Deleted` = '%d'", $value)
            ->set   ("`Date` = `Date` + 1")
            ->where ("`ID` = '%d'", $id)
            ->compile();
        echo $query->getQuery();
        /*
UPDATE `DB_Posts` AS `Posts`
SET `Deleted` = '1',
    `Date` = `Date` + 1
WHERE `ID` = '5'
         */
    }

    public function update2 ($value = 1) {
        $query = $this->getQuery()
            ->update ()
            ->table  ('Posts', 'Post')
            ->innerJoin('Authors', 'Author', '`Author`.`ID` = `Post`.`AuthorID`')
            ->set    ("`Deleted` = '%d'", $value)
            ->where  ("`Author`.`Name` LIKE '%%%s%%'", 'hoc')
            ->orWhere("`Author`.`Name` LIKE '%%%s%%'", 'iki')
            ->limit (2)
            ->compile();
        echo $query->getQuery();
        /*
UPDATE `DB_Posts` AS `Post`
INNER JOIN `DB_Authors` AS `Author`
  ON `Author`.`ID` = `Post`.`AuthorID`
SET `Deleted` = '1'
WHERE `Author`.`Name` LIKE '%hoc%'
  OR `Author`.`Name` LIKE '%iki%'
LIMIT 2
        */
    }

    public function insert ($value = 1) {
        $id = 5;
        $query = $this->getQuery()
            ->insert ()
            ->table  ('Posts')
            ->fields ('ID','Text','AuthorID','Date','Deleted')
            ->values (NULL , 'Tes"t7', '3', time(), '0')
            ->values (array(NULL , 'Test8', '2', time(), '0'))
            ->compile();
        echo $query->getQuery();

        /*
INSERT INTO `DB_Posts` (
`ID`,`Text`,`AuthorID`,`Date`,`Deleted`
) VALUES (
NULL , 'Test7', '3', '1234567970', '0'
), (
NULL , 'Test8', '2', '1234567980', '0'
);
         */
    }

    public function delete () {
        $id = 4;
        $query = $this->getQuery()
            ->delete  ()
            ->table   ('Posts')
            ->where   ("`ID` > '%d'", $id)
            ->limit   (1)
            ->compile ();
        echo $query->getQuery();

       /*
DELETE FROM `DB_Posts` AS `Posts`
WHERE `ID` > '4'
LIMIT 1
        */
    }

}